﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;

namespace Game.Handlers
{

    public class DBManager
    {
        private string StartUPPath = Application.StartupPath;
       // private string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\DB\\Database.accdb;Jet OLEDB;Persist Security Info=False;MultipulAciveResultSets=true";
        private string ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + Application.StartupPath + "\\DB\\Database1.accdb";
        public OleDbConnection oconn;
        public OleDbDataReader oreader;
       // public OleDbCommand ocomm;
        public OleDbCommand ocomm;

        #region Reader
        /// <summary>
        /// Reads SQL Table
        /// </summary>
        /// <param name="DBQuery">Reads data from SQL DB, DBQuery =  string DBQuery = "SELECT * FROM `table` WHERE ID = "+ "'"+charID+"'";</param>
        public void ORead(string DBQuery)
        {
            try
            {
                ocomm = new OleDbCommand(DBQuery);
                oconn = new OleDbConnection(ConnString);
                ocomm.Connection = oconn;
                ocomm.CommandTimeout = 10000;
                oconn.Open();
                oreader = ocomm.ExecuteReader();
            }
            catch (OleDbException me)
            {
                    DBLogger(me, DBQuery);
            }
        }
        #endregion
        #region Updater
        /// <summary>
        /// Updates Data in SQL Table
        /// </summary>
        /// <param name="DBQuery">Updates data in SQL DB, DBQuery = string DBQuery = "UPDATE `table` SET `collum` = "+data+" WHERE ID = "'"+charID+"'";</param
        public void OUpdate(string DBQuery)
        {
            try
            {
                oconn = new OleDbConnection(ConnString);
                ocomm = new OleDbCommand(DBQuery);
                ocomm.Connection = oconn;
                ocomm.CommandTimeout = 10000;
                if (oconn.State == 0)
                {
                    oconn.Open();
                }
                ocomm.ExecuteNonQuery();
                //return;
                //return ocomm.ExecuteNonQuery();
                
            }
            catch (OleDbException me)
            {
                   DBLogger(me, DBQuery);
            }
        }

        #endregion
        #region Inserter
        /// <summary>
        /// Inserts data to SQL DB
        /// </summary>
        /// <param name="DBQuery">Insert data into the SQL db, DBQuery = INSERT INTO `dbname`  VALUES (`item1_value`, `item2_value`, `etc`)  </param>
        public void OInsert(string DBQuery)
        {
            try
            {
                oconn = new OleDbConnection(ConnString);
                ocomm = new OleDbCommand(DBQuery);
                ocomm.Connection = oconn;
                ocomm.CommandTimeout = 10000;
                if (oconn.State == 0)
                {
                    oconn.Open();
                }
                ocomm.ExecuteNonQuery();
            }
            catch (OleDbException me)
            {
                    DBLogger(me, DBQuery);
            }
        }
        #endregion
        #region Deleter
        /// <summary>
        /// Deletes data from the SQL Table
        /// </summary>
        /// <param name="DBQuery">SQL Query to execute DELETE from SQL DB, DBQuery = DELETE FROM `database` WHERE (`Field` = {value})</param>
        public void ODelete(string DBQuery)
        {
            try
            {
                oconn = new OleDbConnection(ConnString);
                ocomm = new OleDbCommand(DBQuery);
                ocomm.Connection = oconn;
                ocomm.CommandTimeout = 10000;
                if (oconn.State == 0)
                {
                    oconn.Open();
                }
                ocomm.ExecuteNonQuery();
            }
            catch (OleDbException me)
            {
                    DBLogger(me, DBQuery);
            }
        }
        #endregion

        #region ReadDataIntoTable

        public DataTable DT(string DBQuery)
        {
            DataSet ds = new DataSet();
            try
            {
                oconn = new OleDbConnection(ConnString);
                OleDbDataAdapter oda = new OleDbDataAdapter(DBQuery, oconn);
                oda.Fill(ds);
            }
            catch (OleDbException me)
            {
                    DBLogger(me, DBQuery);
            }
            oclose();
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        #endregion

        #region DBLogger
        private void DBLogger(OleDbException oe, string DBQuery)
        {
            FileInfo t = new FileInfo("SqlError.log");
            if (t.Exists == true)
            {
                TextWriter tex = new StreamWriter(t.OpenWrite());
                tex.WriteLine("Date/Time: " + DateTime.Now.ToString());
                tex.WriteLine(" ");
                tex.WriteLine("Sql String: " + DBQuery);
                tex.WriteLine(" ");
                tex.WriteLine("Sql Error: ");
                tex.WriteLine(oe);
                tex.Write(tex.NewLine);
                tex.Flush();
                tex.Close();
                tex = null;
                t = null;
            }
            else
            {

                StreamWriter sw = t.CreateText();
                sw.WriteLine("Date/Time: " + DateTime.Now.ToString());
                sw.WriteLine(" ");
                sw.WriteLine("Sql String: " + DBQuery);
                sw.WriteLine(" ");
                sw.WriteLine("Sql Error: ");
                sw.WriteLine(oe);
                sw.Write(sw.NewLine);
                sw.Flush();
                sw.Close();
                sw = null;
                t = null;
            }
        }
        #endregion

        #region Close
        public void oclose()
        {
            try
            {
                oconn.Close();
            }
            catch { }
            try
            {
                oreader.Close();
            }
            catch { }
        }
        #endregion

        #region Dispose

        public void Dispose()
        {
            try
            {
                ocomm.Dispose();
            }
            catch { }
            try
            {
                oreader.Close();
            }
            catch { }
            try
            {
                oconn.Close();
                oconn.Dispose();
            }
            catch { }
        }
        #endregion
    }
}
